-- DATABASE MANAGEMENT SYSTEMS 2019/20 
-- Assignment 1
-- Matthew Howells (1702520)
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--Drop Tables
--DROP TABLE Region
--DROP TABLE City
--DROP TABLE Gym
--DROP TABLE MembershipType
--DROP TABLE GymMembershipPrice
--DROP TABLE Customer
--DROP TABLE MembershipPurchase

-- Create Database
CREATE DATABASE ShapeupGymCaseStudy 
Go

-- Create Region Table
CREATE TABLE Region(
	RegionCode		CHAR(8) NOT NULL 
	CONSTRAINT PK_Region PRIMARY KEY,
	RegionName VARCHAR(30) NOT NULL
);

GO

-- Create City Table
CREATE TABLE City(
	CityCode	CHAR(4) NOT NULL 
	CONSTRAINT PK_CityCode PRIMARY KEY,
	CityName	VARCHAR(20) NOT NULL,
	CountyName  VARCHAR(20) NOT NULL,
	RegionCode	CHAR(8) NOT NULL 
	CONSTRAINT FK_City_RegionCode FOREIGN KEY REFERENCES Region(RegionCode),
);

GO

-- Create Gym Table
CREATE TABLE Gym(
	GymBranchID	CHAR(8) NOT NULL 
	CONSTRAINT PK_GymBranchID PRIMARY KEY,
	CONSTRAINT CHK_Branch_GymBranchID CHECK(GymBranchID LIKE 'G[0-9][0-9][0-9][0-9]'),
	Name	VARCHAR(20) NOT NULL,
	Address  VARCHAR(40) NOT NULL,
	CityCode CHAR(4) 
	CONSTRAINT FK_Gym_CityCode FOREIGN KEY REFERENCES City(CityCode),
	Postcode VARCHAR(8) NOT NULL
	CONSTRAINT CHK_Gym_Postcode CHECK (Len(Postcode) <= 8),
	TelephoneNo VARCHAR(12) NOT NULL,
);

GO

-- Create Membership Type Table
CREATE TABLE MembershipType(
	MembershiptypeID VARCHAR(10) NOT NULL CONSTRAINT PK_MembershiptypeID PRIMARY KEY,
	PackageName	VARCHAR(30) NOT NULL,
	Description VARCHAR(1000) NOT NULL,
	ContractType CHAR(100),
	DurationType VARCHAR(365) NOT NULL,
	DurationQuantity VARCHAR(60) NOT NULL,
	OffPeakMembership VARCHAR(60) NOT NULL,
    CancelAnytime VARCHAR(11) NOT NULL,
);

GO

-- Create Gym Membership Price Table
CREATE TABLE GymMembershipPrice(
	MembershipTypeID VARCHAR(10) NOT NULL
	CONSTRAINT FK_GymMembershipPrice_MembershipTypeID FOREIGN KEY REFERENCES MembershipType(MembershipTypeID),
	GymID CHAR(8) NOT NULL,
	OneTimePaymentFee VARCHAR(8) NOT NULL,
	MonthlyPaymentFee CHAR(8),
	JoiningFee VARCHAR(8) NOT NULL,
);

GO


CREATE TABLE Customer(
	CustomerID CHAR(10) NOT NULL CONSTRAINT PK_CustomerID PRIMARY KEY, 
	GymID CHAR(8) NOT NULL
	CONSTRAINT FK_Customer_GymID FOREIGN KEY REFERENCES Gym(GymBranchID),
	FirstName VARCHAR(60) NOT NULL,
	LastName VARCHAR(60) NOT NULL,
	DOB		DATE NOT NULL,
	Email	VARCHAR(100) NOT NULL,
	Passcode VARCHAR(30) NOT NULL,
	HomeAddress VARCHAR(30) NOT NULL,
	HomeCityCode VARCHAR(30) NOT NULL,
	Postcode VARCHAR(30) NOT NULL , 
	CONSTRAINT CHK_Customer_Postcode CHECK(Len(Postcode) >= 6),
	ContactPhoneNo VARCHAR(25) NOT NULL,
	MedicalSummary VARCHAR(600),
	JoinDate DATE NOT NULL, 
	BankAccountNo CHAR(20),
	SortCode CHAR(10),

);

GO

CREATE TABLE MembershipPurchase(
	PurchaseNo VARCHAR(10) NOT NULL CONSTRAINT PK_PurchaseNo PRIMARY KEY, 
	CustomerID CHAR(10) NOT NULL
	CONSTRAINT FK_MembershipPurchase_CustomerID FOREIGN KEY REFERENCES Customer(CustomerID),
	MembershipTypeID VARCHAR(10) NOT NULL
	CONSTRAINT FK_MembershipPurchase_MembershipTypeID FOREIGN KEY REFERENCES MembershipType(MembershipTypeID),
	StartDate DATE NOT NULL,
	EndDate	DATE NOT NULL,
	PurchaseDate DATE NOT NULL,
	JoiningFeePrice DECIMAL NOT NULL,
	OneTimeFeePrice DECIMAL NOT NULL,
	MonthlyFeePrice DECIMAL NOT NULL,
	MonthlyPaymentDay VARCHAR,
	LastPaymentDate DATE NOT NULL,
	Status CHAR NOT NULL, 
);

GO
